<?php
namespace Admin\Controller;

/**
 * 查询统计
 */
class StatisticsController extends AdminController{

	public function index(){
		$GoodsType = M("GoodsType");
        $goodstypelist = $GoodsType->where('status=1')->select();
		$gnum = count($goodstypelist);

		for ($i = 0; $i < $gnum; $i++) {
			$goodstype = $goodstypelist[$i];
			$condition['OrderZB.status'] = 1;
			$condition['type_id'] = $goodstype["id"];
			$OrderView = D('OrderView');
			$orderlist = $OrderView->field('goods_name,spe_name,sum(num) as totalnum')->where($condition)->group('spe_id')->select();
			$goodstypelist[$i]["orderlist"] = $orderlist;
		}
		$this->meta_title = '待发货订单统计';
		$this->assign('typelist',$goodstypelist);
		$this->display();
	}

    public function orderstat(){
    	$this->meta_title = '订单统计';
		$this->display();
    }

    /**
     * 根据商品统计订单
     */
    public function orderstatbygoods(){
    	$status = I('status',null);
		$stime = I('time-start');
		$etime = I('time-end');
		$isgg = I('isgg');
		if(!empty($status) || $status == '0'){
			$map['status'] = $status;
		}else{
			$map['status'] = array('GT',0);
		}
		if(!empty($stime)){
			$map['orderdate'][] = array('EGT',strtotime($stime));
		}
		if(!empty($etime)){
			$map['orderdate'][] = array('ELT',strtotime($etime));
		}
		$OrderView = D('OrderView');
		if($isgg == "on"){
			$list = $OrderView->field('goods_name,spe_name,sum(num) as totalnum,sum(OrderDetail.total) as totalje')->where($map)->group('goods_id')->order('type_id')->select();
		}else{
			$list = $OrderView->field('goods_name,spe_name,sum(num) as totalnum,sum(OrderDetail.total) as totalje')->where($map)->group('spe_id')->order('type_id')->select();
		}
		$zsum = $OrderView->where($map)->sum('OrderDetail.total');
		$this->assign('status',$status);
		$this->assign('stime',$stime);
		$this->assign('etime',$etime);
		$this->assign('zsum',$zsum);
		$this->assign('_list',$list);
    	$this->meta_title = '订单统计-按商品统计';
		$this->display();
    }

    /**
     * 根据日期统计订单
     */
    public function orderstatbydate(){
    	$status = I('status',null);
		$stime = I('time-start');
		$etime = I('time-end');
		if(!empty($status) || $status == '0'){
			$map['status'] = $status;
		}else{
			$map['status'] = array('GT',0);
		}
		if(!empty($stime)){
			$map['orderdate'][] = array('EGT',strtotime($stime));
		}
		if(!empty($etime)){
			$map['orderdate'][] = array('ELT',strtotime($etime));
		}
		$OrderView = D('OrderView');
		$list = $OrderView->field(array('sum(OrderDetail.total) as totalje','FROM_UNIXTIME(OrderZB.orderdate,"%Y%m%d") odate'))->where($map)->group('odate')->order('odate')->select();
		$zsum = $OrderView->where($map)->sum('OrderDetail.total');
		$this->assign('status',$status);
		$this->assign('stime',$stime);
		$this->assign('etime',$etime);
		$this->assign('zsum',$zsum);
		$this->assign('_list',$list);
    	$this->meta_title = '订单统计-按日期统计';
		$this->display();
    }

    /**
     * 根据客户统计订单
     */
    public function orderstatbycus(){
    	$status = I('status',null);
		$stime = I('time-start');
		$etime = I('time-end');
		if(!empty($status) || $status == '0'){
			$map['status'] = $status;
		}else{
			$map['status'] = array('GT',0);
		}
		if(!empty($stime)){
			$map['orderdate'][] = array('EGT',strtotime($stime));
		}
		if(!empty($etime)){
			$map['orderdate'][] = array('ELT',strtotime($etime));
		}
		$OrderView = D('OrderView');
		$list = $OrderView->field(array('sum(OrderDetail.total) as totalje','customers_name','customers_id'))->where($map)->group('customers_id')->order('customers_id')->select();
		$zsum = $OrderView->where($map)->sum('OrderDetail.total');

		foreach ($list as $key => $row){
			$totalje = $row['totalje'];
			$cusid = $row['customers_id'];
			$map['customers_id'] = $cusid;
			$map['receipt_num'] = array('exp',' is not null');
			$totaljey = $OrderView->field(array('sum(OrderDetail.total) as totalje'))->where($map)->find();
			
			if(is_null($totaljey['totalje'])){
				$list[$key]['totaljey'] = '0.00';
			}else{
				$list[$key]['totaljey'] = $totaljey['totalje'];
			}
			$list[$key]['totaljew'] = $totalje - $totaljey['totalje'];
	    }
		$this->assign('status',$status);
		$this->assign('stime',$stime);
		$this->assign('etime',$etime);
		$this->assign('zsum',$zsum);
		$this->assign('_list',$list);
    	$this->meta_title = '订单统计-按客户统计';
		$this->display();
    }

    /**
     * 代发货-导出Excel
     */
    public function expOrderFordfh(){//导出Excel
        $xlsName  = "代发货商品统计";
        $xlsCell  = array(
	        array('type_name','商品分类'),
	        array('goods_name','商品名称'),
	        array('spe_name','规格'),
	        array('totalnum','代发货数量')   
        );
        $condition['OrderZB.status'] = 1;
		$OrderView = D('OrderView');
		$xlsData = $OrderView->field('type_name,goods_name,spe_name,sum(num) as totalnum')->where($condition)->group('spe_id')->select();
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
    }

    /**
     * 按商品统计订单-导出Excel
     */
    public function expOrderForgoods(){//导出Excel
        $xlsName  = "按商品订单统计";
        $xlsCell  = array(
	        array('goods_name','商品名称'),
	        array('spe_name','规格'),
	        array('totalnum','数量'),
	        array('totalje','金额') 
        );
        $status = I('status',null);
		$stime = I('time-start');
		$etime = I('time-end');
		$isgg = I('isgg');
		if(!empty($status) || $status == '0'){
			$map['status'] = $status;
		}else{
			$map['status'] = array('GT',0);
		}
		if(!empty($stime)){
			$map['orderdate'][] = array('EGT',strtotime($stime));
		}
		if(!empty($etime)){
			$map['orderdate'][] = array('ELT',strtotime($etime));
		}
		$OrderView = D('OrderView');
		if($isgg == "on"){
			$xlsData = $OrderView->field('goods_name,spe_name,sum(num) as totalnum,sum(OrderDetail.total) as totalje')->where($map)->group('goods_id')->order('type_id')->select();
		}else{
			$xlsData = $OrderView->field('goods_name,spe_name,sum(num) as totalnum,sum(OrderDetail.total) as totalje')->where($map)->group('spe_id')->order('type_id')->select();
		}
		$zsum = $OrderView->where($map)->sum('OrderDetail.total');
        $this->exportExcelByOrderstat($xlsName,$xlsCell,$xlsData,$zsum);
    }

    /**
     * 按日期统计订单-导出Excel
     */
    public function expOrderFordate(){//导出Excel
        $xlsName  = "按日期订单统计";
        $xlsCell  = array(
	        array('odate','日期'),
	        array('totalje','金额')
        );
        $status = I('status',null);
		$stime = I('time-start');
		$etime = I('time-end');
		if(!empty($status) || $status == '0'){
			$map['status'] = $status;
		}else{
			$map['status'] = array('GT',0);
		}
		if(!empty($stime)){
			$map['orderdate'][] = array('EGT',strtotime($stime));
		}
		if(!empty($etime)){
			$map['orderdate'][] = array('ELT',strtotime($etime));
		}
		$OrderView = D('OrderView');
		$xlsData = $OrderView->field(array('sum(OrderDetail.total) as totalje','FROM_UNIXTIME(OrderZB.orderdate,"%Y%m%d") odate'))->where($map)->group('odate')->order('odate')->select();
		$zsum = $OrderView->where($map)->sum('OrderDetail.total');
        $this->exportExcelByOrderstat($xlsName,$xlsCell,$xlsData,$zsum);
    }

    /**
     * 按客户统计订单-导出Excel
     */
    public function expOrderForcus(){//导出Excel
        $xlsName  = "按客户订单统计";
        $xlsCell  = array(
	        array('customers_name','客户'),
	        array('totalje','金额'),
	        array('totaljey','已收款'),
	        array('totaljew','未收款')
        );
        $status = I('status',null);
		$stime = I('time-start');
		$etime = I('time-end');
		if(!empty($status) || $status == '0'){
			$map['status'] = $status;
		}else{
			$map['status'] = array('GT',0);
		}
		if(!empty($stime)){
			$map['orderdate'][] = array('EGT',strtotime($stime));
		}
		if(!empty($etime)){
			$map['orderdate'][] = array('ELT',strtotime($etime));
		}
		$OrderView = D('OrderView');
		$xlsData = $OrderView->field(array('sum(OrderDetail.total) as totalje','customers_name'))->where($map)->group('customers_id')->order('customers_id')->select();
		$zsum = $OrderView->where($map)->sum('OrderDetail.total');
		foreach ($xlsData as $key => $row){
			$totalje = $row['totalje'];
			$cusid = $row['customers_id'];
			$map['customers_id'] = $cusid;
			$map['receipt_num'] = array('exp',' is not null');
			$totaljey = $OrderView->field(array('sum(OrderDetail.total) as totalje'))->where($map)->find();
			
			if(is_null($totaljey['totalje'])){
				$xlsData[$key]['totaljey'] = '0.00';
			}else{
				$xlsData[$key]['totaljey'] = $totaljey['totalje'];
			}
			$xlsData[$key]['totaljew'] = $totalje - $totaljey['totalje'];
	    }
		
        $this->exportExcelByOrderstat($xlsName,$xlsCell,$xlsData,$zsum);
    }
}